#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   intellent_test.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2022-05-31 15:55   pengwei.sun      1.0         None
'''
import datetime
import pandas as pd
import numpy as np
import os,sys
sys.path.append(os.getcwd())
from src.utils.config import logger
from src.mobile.levelrate.config_utils import s_min_max_range_fun, sale_num_price_fun
from src.utils.dingding_message import dingding_messager
from src.utils.db_processor import mysql_prediction_processor,presto_processor
from src.utils.util import get_today, check_date_str, format_date_string, format_date
from src.mobile.levelrate.model_template_level2_rate_v1_intellent import process_template_level_rate_final
import time
import pickle
from src.mobile.levelrate.config_intelligent_utils import level_rank_df,product_level_df,apple_product_ids
from src.mobile.levelrate.reverse.sku2_skulevel_period_price import get_period_price_fun
from src.mobile.levelrate.reverse.function_utils import save_pickle_data, load_pickle_data

from multiprocessing import Pool



def level_limit_price_fun(row):
    rate_price_up = row['rate_price_up']
    rate_price_down = row['rate_price_down']
    rate_price_mid = row['rate_price_mid']
    product_level_order_rank = row['product_level_order_rank']
    product_level_order_rank1 = row['product_level_order_rank1']
    product_level_order_rank2 = row['product_level_order_rank2']
    mean_rate = row['mean_rate']
    lastprice = row['lastprice']
    sale_cnt = row['sale_cnt']
    # rt_price=lastprice
    if product_level_order_rank2==-1:
            rt_price=lastprice
    elif sale_cnt>0:
            rt_price = lastprice
    else:

            if product_level_order_rank<=product_level_order_rank1:
                    rt_price = rate_price_up*mean_rate
            elif product_level_order_rank>=product_level_order_rank2:
                    rt_price = rate_price_down * mean_rate
            else:
                    rt_price = rate_price_mid * mean_rate


    return rt_price

def process_maximum_price_fun(intellent_df_tmp,by_column='product_sku_id'):
    grouped = intellent_df_tmp.groupby(by='product_sku_id')

    for name, group in grouped:
        size = group.shape[0]

        group = group.sort_values(['product_level_order_rank'], ascending=True)

        group.reset_index(drop=True, inplace=True)
        if size>1:
            first_price = group.loc[0,'cal_rate_price']
            second_price = group.loc[1,'cal_rate_price']
            rate = first_price/second_price
            if rate>1.2:
                group_column =group.loc[0, by_column]
                level_id =group.loc[0, 'level_id']
                intellent_df_tmp.loc[(intellent_df_tmp[by_column]==group_column) & (intellent_df_tmp.level_id==level_id),'cal_rate_price']=second_price*1.2
                intellent_df_tmp.loc[(intellent_df_tmp[by_column]==group_column) & (intellent_df_tmp.level_id==level_id),'first_second_flag']=1
    return intellent_df_tmp

cpu_worker_num=4
rs_file = 'intellent_result_df.pkl'
result_df = load_pickle_data(rs_file)
template_rate_df = pd.read_csv('/data/sunpengwei/tmp/intellent_template_level_rate.csv', encoding='utf-8-sig')
template_rate_df = template_rate_df.merge(level_rank_df[['product_level_id', 'product_level_order_rank']], left_on=['level_id'],right_on=['product_level_id'])
template_rate_df = template_rate_df.drop(columns=['product_level_id'],axis=1)
template_rate_df.loc[template_rate_df.template_brand=='242_52']
template_rate_df.loc[template_rate_df.mean_Rate>1.5]
result_df.loc[result_df.product_id==50264]
filter_product_rate = result_df.loc[result_df.product_id.isin(set(result_df.loc[result_df.w_mean>0,'product_id'].drop_duplicates()))]

# def process_maximum_ratio_fun(filter_product_rate,result_df,by_column='product_id',cal_rate='mean_rate'):
#     grouped = filter_product_rate.groupby(by=by_column)
#     # grouped = price_df.groupby(['property_template_brand', 'product_sku_key'])
#     for name, group in grouped:
#         size = group.shape[0]
#
#         group = group.sort_values(['product_level_order_rank'], ascending=True)
#         # group['levelname']=group.product_level_name
#         group.reset_index(drop=True, inplace=True)
#         if size>1:
#             first_rate = group.loc[0,'mean_rate']
#             second_rate = group.loc[1,'mean_rate']
#             rate = first_rate/second_rate
#             if rate>1.2:
#                 group_column =group.loc[0, by_column]
#                 level_id =group.loc[0, 'level_id']
#                 result_df.loc[(result_df[by_column]==group_column) & (result_df.level_id==level_id),cal_rate]=second_rate*1.2
#     return result_df

# result_df = process_maximum_ratio_fun(filter_product_rate,result_df,by_column='product_id',cal_rate='mean_rate')
# filter_product_rate['level_cnt'] = filter_product_rate.groupby(by='product_id')['level_id'].count()
product_level_cnt = filter_product_rate.groupby(by='product_id')['level_id'].count().reset_index()
product_level_cnt1 = filter_product_rate.loc[filter_product_rate.w_mean>0].groupby(by='product_id')['level_id'].count().reset_index()
product_level_cnt = product_level_cnt.rename(columns={'level_id': 'level_cnt'})
product_level_cnt1 = product_level_cnt1.rename(columns={'level_id': 'level_valid_cnt'})
product_level_cnt = product_level_cnt.merge(product_level_cnt1,on='product_id')
product_level_cnt['product_valid_rate'] = product_level_cnt['level_valid_cnt']*1.0/product_level_cnt['level_cnt']

filter_template_rate = template_rate_df.loc[template_rate_df.template_brand.isin(set(template_rate_df.loc[template_rate_df.w_mean>0,'template_brand'].drop_duplicates()))]
# filter_product_rate['level_cnt'] = filter_product_rate.groupby(by='product_id')['level_id'].count()
# result_df = process_maximum_ratio_fun(filter_template_rate,result_df,by_column='template_brand',cal_rate='template_mean_rate')

template_level_cnt = filter_template_rate.groupby(by='template_brand')['level_id'].count().reset_index()
template_level_cnt1 = filter_template_rate.loc[filter_template_rate.w_mean>0].groupby(by='template_brand')['level_id'].count().reset_index()
template_level_cnt = template_level_cnt.rename(columns={'level_id': 'template_level_cnt'})
template_level_cnt1 = template_level_cnt1.rename(columns={'level_id': 'template_level_valid_cnt'})
template_level_cnt = template_level_cnt.merge(template_level_cnt1,on='template_brand')
template_level_cnt['template_valid_rate'] = template_level_cnt['template_level_valid_cnt']*1.0/template_level_cnt['template_level_cnt']

result_df['product_id'] =result_df.product_id.astype(np.int64)
product_level_cnt['product_id'] =product_level_cnt.product_id.astype(np.int64)
result_df=result_df.merge(product_level_cnt,how='left',on='product_id')
result_df=result_df.merge(template_level_cnt,how='left',on='template_brand')

query_sql="""
select distinct product_id,product_category_id from dim.dim_product
where product_category_id in (22,23,24,25,26,72,77,81,3,64,28,86,63,68,70,71,78,79,65,67,66,80,73,69,82,62,106,113,129) 
"""
product_id_df = presto_processor.load_sql(query_sql)
result_df['product_id'] =result_df.product_id.astype(np.int64)
product_id_df['product_id'] =product_id_df.product_id.astype(np.int64)
rt_df = result_df.merge(product_id_df,how='inner',on='product_id')

# rt_df= rt_df.loc[~(rt_df.product_id.isin(list(rt_df.loc[rt_df.mean_rate<0,'product_id'])))]
# rt_df.loc[rt_df.mean_rate<0,'product_id'].values()

#获取型号对应的等级模板
PRODUCT_TEMPLATE_SQL = """
SELECT distinct
    dp.product_id,
    CASE WHEN otpmp.propertyleveltemplateid IS NULL THEN -1 ELSE otpmp.propertyleveltemplateid END AS product_level_template_id,
    otpm.secondary_level_template_id AS secondary_level_template_id
FROM dim.dim_product dp 
JOIN dim.dim_product_sku_channel_mapping dps ON dp.product_id = dps.product_id and dps.business_channel_id=1 
JOIN ods.ods_trunk_producttemplatemapping otpmp ON (dp.product_id = otpmp.productid AND otpmp.templatetype = 1 and otpmp.business_channel = '0')
JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id and otpm.business_channel=1
WHERE dp.product_category_id in (22,23,24,25,26,72,77,81,3,64,28,86,63,68,70,71,78,79,65,67,66,80,73,69,82,62,106,113,129) and otpm.business_channel=1 and dps.business_channel_id=1
"""
product_template_df = presto_processor.load_sql(PRODUCT_TEMPLATE_SQL)

query_artificial_rate_sql="""
select secondary_level_template_id,product_level_id,mean_rate as artificial_mean_rate from intellent_artificial_level_template_levelrate
"""
query_intelletnt_df = mysql_prediction_processor.load_sql(query_artificial_rate_sql)

query_intelletnt_sql="""
select *
 from price_prediction_photography_bi_price 
where create_date='2022-06-02' and product_category_id  in (22,23,24,25,26,72,77,81,3,64,28,86,63,68,70,71,78,79,65,67,66,80,73,69,82,62,106,113,129)

order by product_level_order_rank
"""

intellent_df = mysql_prediction_processor.load_sql(query_intelletnt_sql)
intellent_df = intellent_df.drop(columns=['product_category_id','product_name','product_level_order_rank','product_level_name'],axis=1)
intellent_df_tmp= intellent_df.merge(rt_df,how='left',on=['product_id','product_level_id'])

intellent_df_tmp['template_valid_rate']=intellent_df_tmp['template_valid_rate'].fillna(-1)
intellent_df_tmp['product_valid_rate']=intellent_df_tmp['product_valid_rate'].fillna(-1)

intellent_df_tmp =intellent_df_tmp.merge(product_template_df,how='left',on='product_id')
intellent_df_tmp =intellent_df_tmp.merge(query_intelletnt_df,how='left',on=['secondary_level_template_id','product_level_id'])

intellent_df_tmp['mean_rate_flag']='0'
intellent_df_tmp.loc[(intellent_df_tmp['product_valid_rate']<0.5) & (intellent_df_tmp['template_valid_rate']>=0.5),'mean_rate']=\
    intellent_df_tmp.loc[(intellent_df_tmp['product_valid_rate']<0.5) & (intellent_df_tmp['template_valid_rate']>=0.5),'template_mean_rate']

intellent_df_tmp.loc[(intellent_df_tmp['product_valid_rate']<0.5) & (intellent_df_tmp['template_valid_rate']>=0.5),'mean_rate_flag']=1

intellent_df_tmp.loc[(intellent_df_tmp['product_valid_rate']<0.5) & (intellent_df_tmp['template_valid_rate']<0.5),'mean_rate']=\
    intellent_df_tmp.loc[(intellent_df_tmp['product_valid_rate']<0.5) & (intellent_df_tmp['template_valid_rate']<0.5),'artificial_mean_rate']

intellent_df_tmp.loc[(intellent_df_tmp['product_valid_rate']<0.5) & (intellent_df_tmp['template_valid_rate']<0.5),'mean_rate_flag']=2



product_sku_id_list = set(intellent_df_tmp.loc[(intellent_df_tmp.mean_rate<0),'product_sku_id']) #等级比小于0的sku

product_sku_id_all_list = set(intellent_df_tmp['product_sku_id']) #全量sku
product_sku_id_list1 = set(intellent_df_tmp.loc[(intellent_df_tmp.sale_cnt>0),'product_sku_id']) #售卖数量>0之外的sku
product_sku_id_list1 = product_sku_id_all_list-product_sku_id_list1
product_sku_id_list =product_sku_id_list.union(product_sku_id_list1)



# intellent_df_tmp.loc[intellent_df_tmp['product_sku_id'].isin(product_sku_id_list),'mean_rate']=intellent_df_tmp.loc[intellent_df_tmp['product_sku_id'].isin(product_sku_id_list),'artificial_mean_rate']
intellent_df_tmp['artificial_mean_rate_flag']=0
intellent_df_tmp.loc[intellent_df_tmp['product_sku_id'].isin(product_sku_id_list),'artificial_mean_rate_flag']=1 #售卖数量>0之外的sku都需要基于最高等级计算等级比价格

intellent_df_tmp =intellent_df_tmp.drop(columns=['product_level_order_rank','product_level_name'],axis=1)
intellent_df_tmp = intellent_df_tmp.merge(level_rank_df[['product_level_id', 'product_level_order_rank']], on=['product_level_id'])

intellent_df_tmp =intellent_df_tmp.sort_values(by=['product_sku_id','product_level_order_rank','product_level_id'])
# intellent_df_tmp.loc[intellent_df_tmp['artificial_mean_rate_flag']==1]
intellent_df_tmp['sale_cnt_bak']=intellent_df_tmp['sale_cnt']

intellent_df_tmp['level_rank_rank'] = intellent_df_tmp.groupby(intellent_df_tmp['product_sku_id'])['product_level_order_rank'].rank(
        ascending=True, method='first')
intellent_df_tmp.loc[(intellent_df_tmp['level_rank_rank']==1) & (intellent_df_tmp['artificial_mean_rate_flag']==1),'sale_cnt']=1000

inte_df = intellent_df_tmp.loc[intellent_df_tmp.sale_cnt>0]
inte_df['count_rank'] = inte_df.groupby(inte_df['product_sku_id'])['sale_cnt'].rank(
        ascending=False, method='first')
inte_rank_df = inte_df.loc[inte_df.mean_rate>0,['product_sku_id','sale_cnt','count_rank','mean_rate','lastprice','product_level_order_rank']].sort_values(by=['product_sku_id','count_rank'])
inte_rank_df = inte_rank_df.loc[inte_rank_df.count_rank<=2]

inte_rank_df['count_rank'] = inte_rank_df.groupby(inte_rank_df['product_sku_id'])['product_level_order_rank'].rank(
        ascending=True, method='first')

inte_rank2_df=inte_rank_df.copy()
inte_rank2_df['count_rank2']=inte_rank2_df['count_rank']-1
inte_rank2_df['mean_rate2']=inte_rank2_df['mean_rate'].copy()
inte_rank2_df =inte_rank2_df[['product_sku_id','count_rank2','mean_rate2','lastprice','product_level_order_rank','sale_cnt']]
inte_rank2_df = inte_rank2_df.rename(columns={'lastprice': 'lastprice2','product_level_order_rank':'product_level_order_rank2','sale_cnt':'sale_cnt2'})

inte_rank_df = inte_rank_df.merge(inte_rank2_df,how='left',left_on=['product_sku_id','count_rank'],right_on=['product_sku_id','count_rank2'])
inte_rank_df = inte_rank_df.loc[inte_rank_df.count_rank==1]

inte_rank_df.loc[~(inte_rank_df.count_rank2>0),'lastprice2']=inte_rank_df.loc[~(inte_rank_df.count_rank2>0),'lastprice']
inte_rank_df.loc[~(inte_rank_df.count_rank2>0),'mean_rate2']=inte_rank_df.loc[~(inte_rank_df.count_rank2>0),'mean_rate']
inte_rank_df.loc[~(inte_rank_df.count_rank2>0),'product_level_order_rank2']=inte_rank_df.loc[~(inte_rank_df.count_rank2>0),'product_level_order_rank']
inte_rank_df['rate_price_up'] = inte_rank_df['lastprice']/inte_rank_df['mean_rate']
inte_rank_df['rate_price_down'] = inte_rank_df['lastprice2']/inte_rank_df['mean_rate2']
inte_rank_df['rate_price_mid'] =(inte_rank_df['rate_price_down']+inte_rank_df['rate_price_up'])/2

# inte_rank_df['rate_price']=-1
# inte_rank_df['diff_price'] = inte_rank_df['lastprice']-inte_rank_df['lastprice2']
# inte_rank_df['diff_rate'] = inte_rank_df['mean_rate']-inte_rank_df['mean_rate2']
# inte_rank_df.loc[inte_rank_df.diff_rate>0,'rate_price']=inte_rank_df.loc[inte_rank_df.diff_rate>0,'diff_price']/inte_rank_df.loc[inte_rank_df.diff_rate>0,'diff_rate']

# inte_rank_df.loc[~(inte_rank_df.diff_rate>0),'rate_price'] = inte_rank_df.loc[~(inte_rank_df.diff_rate>0),'lastprice']/inte_rank_df.loc[~(inte_rank_df.diff_rate>0),'mean_rate']
inte_rank_df = inte_rank_df.rename(columns={'mean_rate': 'mean_rate1','lastprice':'lastprice1','sale_cnt':'sale_cnt1','product_level_order_rank':'product_level_order_rank1'})

intellent_df_tmp = intellent_df_tmp.merge(inte_rank_df,how='left',on='product_sku_id')
intellent_df_tmp['product_level_order_rank2'] = intellent_df_tmp['product_level_order_rank2'].fillna(-1)

intellent_df_tmp['cal_rate_price'] = intellent_df_tmp.apply(level_limit_price_fun,axis=1)

# intellent_df_tmp['cal_rate_price'] = intellent_df_tmp['rate_price']*intellent_df_tmp['mean_rate']
# intellent_df_tmp.loc[intellent_df_tmp.product_level_order_rank>=intellent_df_tmp.product_level_order_rank1,'cal_rate_price'] = intellent_df_tmp.loc[intellent_df_tmp.product_level_order_rank>=intellent_df_tmp.product_level_order_rank1,'rate_price_up']*intellent_df_tmp.loc[intellent_df_tmp.product_level_order_rank>=intellent_df_tmp.product_level_order_rank1,'mean_rate']
# intellent_df_tmp.loc[intellent_df_tmp.product_level_order_rank<=intellent_df_tmp.product_level_order_rank2,'cal_rate_price'] = intellent_df_tmp.loc[intellent_df_tmp.product_level_order_rank<=intellent_df_tmp.product_level_order_rank2,'rate_price_down']*intellent_df_tmp.loc[intellent_df_tmp.product_level_order_rank<=intellent_df_tmp.product_level_order_rank2,'mean_rate']
#
# intellent_df_tmp.loc[intellent_df_tmp.sale_cnt>0,'cal_rate_price'] = intellent_df_tmp.loc[intellent_df_tmp.sale_cnt>0,'lastprice']
# intellent_df_tmp.loc[~(intellent_df_tmp.rate_price_up>0),'cal_rate_price'] = intellent_df_tmp.loc[~(intellent_df_tmp.rate_price_up>0),'lastprice']
# intellent_df_tmp.loc[intellent_df_tmp.sale_cnt>0,'cal_rate_price'] = intellent_df_tmp.loc[intellent_df_tmp.sale_cnt>0,'lastprice']

# intellent_df_tmp.to_csv('/data/sunpengwei/tmp/intellent_level_rate.csv', encoding='utf-8-sig')

BASE_LEVEL_RANK="""
SELECT 
product_level_id,product_level_name,product_level_order_rank as product_level_order_ranknew
FROM dim.dim_product_level a
WHERE product_category_id in (3,64,28,86,63,68,70,71,78,79,65,67,66,80,73,69,82,62,106,113,129,22,23,24,25,26,72,77,81) AND is_product_level_active_flag = 1
"""
level_rank = presto_processor.load_sql(BASE_LEVEL_RANK)
intellent_df_tmp = intellent_df_tmp.merge(level_rank,on='product_level_id')
intellent_df_tmp['rate'] =abs(intellent_df_tmp['cal_rate_price']-intellent_df_tmp['lastprice'])/intellent_df_tmp['lastprice']
intellent_df_tmp['first_second_flag']=0
intellent_df_tmp = process_maximum_price_fun(intellent_df_tmp)
intellent_df_tmp.sort_values(by=['product_sku_id','product_level_order_rank'],ascending=[False,True])

columns_list = ['product_category_name',
'product_brand_id',
'product_brand_name',
'product_id',
'level_template_id',
'template_name',
'price_03',
'price_02',
'price_01',
'saleprice',
'cnt',
'sumprice',
'avgprice',
'thisprice',
'per_rate',
'varrations',
'totalsalecnt',
'product_sku_id',
'product_sku_name',
'product_level_id',
'product_level_name',
'product_level_order_ranknew',
'sale_cnt',
'sale_cnt_bak',
'lastprice',
'cal_rate_price',
'y_pred',
'mean_rate',
'template_mean_rate',
'artificial_mean_rate',
'mean_rate_flag',
'artificial_mean_rate_flag',
'create_time',
'create_date',
'product_category_parent_id',
'template_brand',
'level_id',
'product_name',
'sku_rate1',
'rate_f1',
'sku_rate2',
'rate_f2',
'sku_rate3',
'rate_f3',
'w_f1',
'w_f2',
'w_f3',
'rate_f1_w',
'rate_f2_w',
'rate_f3_w',
'w_mean',
'mean_rate_tmp',
'product_level_order_rank_52',
'level_cnt',
'level_valid_cnt',
'product_valid_rate',
'template_level_cnt',
'template_level_valid_cnt',
'template_valid_rate',
'product_category_id',
'product_level_template_id',
'secondary_level_template_id',
'product_level_order_rank',
'level_rank_rank',
'sale_cnt1',
'count_rank',
'mean_rate1',
'lastprice1',
'product_level_order_rank1',
'count_rank2',
'mean_rate2',
'lastprice2',
'product_level_order_rank2',
'sale_cnt2',
'rate_price_up',
'rate_price_down',
'rate_price_mid',
'rate']
intellent_df_tmp = intellent_df_tmp[columns_list]
intellent_df_tmp.to_csv('/data/sunpengwei/tmp/intellent_level_rate.csv', encoding='utf-8-sig')
print('dsd')